<?php

//use the include feature to grab these methods

function checkRangeSchedule($username, $userPassHash,$startDate,$endDate){
$conn = new mysqli('localhost','root','','SEVENBITS','3306');
if(mysqli_connect_errno()){
echo "connection failed";
}else {
echo "connection successful";
}
$sql = "SELECT * FROM employees,roledate,roles,wdate";
$result = $conn->query($sql);
if($result->num_rows>0){
while($row = $result->fetch_assoc()){
if($row['']){
}

}}

$conn->close();

}

function getDaySchedule($userName, $userPassHash, $Date){

$conn = new mysqli('localhost','root','','SEVENBITS','3306');
if(mysqli_connect_errno()){
echo "connection failed";
}else {
echo "connection successful";
}

//code goes here

$conn->close();

}

function checkIfExsist($userName,$userPassHash){

$conn = new mysqli('localhost','root','','SEVENBITS','3306');
if(mysqli_connect_errno()){
echo "connection failed";
}else {
echo "connection successful<br>";
}

$sql = "SELECT * FROM employees, account WHERE employees.EmpID = account.EmpID";
$result = $conn->query($sql);
if($result->num_rows>0){
while($row = $result->fetch_assoc()){
//echo $row['AccName'] . " : " . $row['EmpID'] . " : " . $userName;
if($row['AccName'] ==$userName ){
echo "true";
$conn->close();
return true;
break;

}

}
}

$conn->close();
echo "false";
return false;
}

function WhatsMyEMpID($userName,$userPassHash){

$conn = new mysqli('localhost','root','','SEVENBITS','3306');
if(mysqli_connect_errno()){
echo "connection failed";
}else {
echo "connection successful<br>";
}

$sql = "SELECT * FROM employees, account WHERE employees.EmpID = account.EmpID";
$result = $conn->query($sql);
if($result->num_rows>0){
while($row = $result->fetch_assoc()){
//echo $row['AccName'] . " : " . $row['EmpID'] . " : " . $userName;
if($row['AccName'] ==$userName ){
echo "true";
$conn->close();
return $row['EmpID'];
break;

}

}
}

$conn->close();
echo "no ID found";
return null;
}

function viewMySchedule($userName,$userPassHash){

//returns sets of {start time, endtime, roleID, DateID}

$conn = new mysqli('localhost','root','','SEVENBITS','3306');
if(mysqli_connect_errno()){
echo "connection failed";
}else {
echo "connection successful<br>";
}

$returnable=array();
if(checkIfExsist($userName,$userPassHash)==true){

$sql = "SELECT * FROM employees,roledate,roles,wdate,account WHERE employees.EmpID = roledate.EmpID AND account.EmpID = employees.EmpID AND roles.RoleID=roledate.RoleID AND wdate.DateID=roledate.DateID";
$result = $conn->query($sql);

$counter =0;
if($result->num_rows>0){
while($row = $result->fetch_assoc()){

if($row['AccName']==$userName && $row['UPassword']=$userPassHash){
//echo $row['StartTime'] . " : " . $row['EndTime'] . " : " . $row['RoleID'] . " : " . $row['DateID'] . " saved and sent to array";
$returnable[count($returnable)]=$row['StartTime'];
$returnable[count($returnable)]=$row['EndTime'];
$returnable[count($returnable)]=$row['RoleID'];
$returnable[count($returnable)]=$row['DateID'];
//echo $counter . " : " . count($returnable) . "<br>";
//$counter ++;
}


}
}
}
return $returnable;
$conn->close();

}

function releaseShift($userName, $userPassHash, $shiftNotDesired){
//return success code{}
$conn = new mysqli('localhost','root','','SEVENBITS','3306');
if(mysqli_connect_errno()){
echo "connection failed";
}else {
echo "connection successful";
}

$shift = json_decode($shiftNotDesired, true);
echo $shift['RoleID'] ;
$sql = "SELECT * FROM roledate";
$result = $conn->query($sql);
$updater = "UPDATE roledate SET 
ShiftStatus = 'Available' WHERE 
`RoleID` = '$shift[RoleID]'
AND `DateID` = '$shift[DateID]'";
if($result->num_rows>0){
while($row = $result->fetch_assoc()){
if($row['RoleID']==$shift['RoleID']&&$row['DateID']==$shift['DateID']){
//echo "<br> id: " . $row['RoleID']."  ".$row['ShiftStatus']. " : " . $row['RequestPendingApproval']. " : ";
echo "this will be updated!" . $shift['DateID'] . " : " . $shift['RoleID'];
//this line updates the selected row
$conn->query($updater);
}
}
}
else{
echo "no Shifts scheduled ";
}



//code goes here

$conn->close();
}

function requestShift($userName, $userPassHash,$desiredShift){
//return result

//this is meant to send to a new table that holds all requests does not change the current status it still counts as available


$conn = new mysqli('localhost','root','','SEVENBITS','3306');
if(mysqli_connect_errno()){
echo "connection failed";
}else {
echo "connection successful";
}
$ID = WhatsMyEMpID($userName,$userPassHash);
$shift = json_decode($desiredShift, true);
//echo $shift['RoleID'] ;
$sql = "SELECT * FROM ProposedChange";
$result = $conn->query($sql);
$updater = "INSERT INTO  `sevenbits`.`ProposedChange` (
`RoleID` ,
`DateID` ,
`MyEmpID` ,
`MyRoleID`,
`MyDateID`
)
VALUES (
'$shift[RoleID]',  '$shift[DateID]',  '$ID',  '0', '0'
);

";//why do you need to insert your own date ID? or role ID ?
//it only makes sense if your swapping shifts not collecting
//put in 0 and 0 as a place holder
if($result->num_rows>0){
while($row = $result->fetch_assoc()){

$conn->query($updater);
}
}




//code goes here



$conn->close();
}

function displayShiftsOffered($userName,$userPassHash){
//returns available shifts sorted
$conn = new mysqli('localhost','root','','SEVENBITS','3306');
if(mysqli_connect_errno()){
echo "connection failed";
}else {
echo "connection successful";
}
$sql = "SELECT * FROM roledate,roles,wdate WHERE roledate.RoleID=roles.RoleID AND roledate.DateID = wdate.DateID";
$result = $conn->query($sql);
if($result->num_rows>0){
echo "<br>The Market is Open!<br>";
while($row = $result->fetch_assoc()){
if($row['ShiftStatus']!="Occupied"){
echo "<br>" . $row['WDate']. "<br>".$row['RoleName'];
//echo '<a href = "Schedule.php?ID=' . urlencode($row['EmpID']),'">Link to Schedule</a>';
}
}
}
else{
echo "No shifts available<br>";
echo "0 results found ";
}
//code goes here

$conn->close();
}

function removeShift($authUser,$authUserPassHash,$shiftToDelete){
$conn = new mysqli('localhost','root','','SEVENBITS','3306');
if(mysqli_connect_errno()){
echo "connection failed";
}else {
echo "connection successful";
}

//code goes here

$conn->close();
}
//Key and value pair K is for what to modify V is for what to change it to 
function modifyShifts($authUser,$authUserPassHash,$shiftToModify,$K,$V){
//returns success or failure message
$conn = new mysqli('localhost','root','','SEVENBITS','3306');
if(mysqli_connect_errno()){
echo "connection failed";
}else {
echo "connection successful";
}

//code goes here

$conn->close();
}
//arrayOfData consists of roleID, DateID, EmpID, StartTime,EndTime, ShiftStatus
function addShift($authUser,$authUserPassHash,$arrayOfData){
$conn = new mysqli('localhost','root','','SEVENBITS','3306');
if(mysqli_connect_errno()){
echo "connection failed";
}else {
echo "connection successful";
}

//code goes here

$conn->close();
}
?>